//
//  LYFDataBaseHandlle.m
//  UI-19 sqlite数据库
//
//  Created by dllo on 16/1/8.
//  Copyright © 2016年 lanou. All rights reserved.
//

#import "LYFDataBaseHandlle.h"
@implementation LYFDataBaseHandlle

+ (LYFDataBaseHandlle *)sharedDataBaseHandle {
    // 静态修饰指针
    static LYFDataBaseHandlle *handle = nil;
    // 线程只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[LYFDataBaseHandlle alloc] init];
    });
    return handle;
}

- (void)openDB {
    
    // 沙盒路径
    NSString *document =  [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 拼接数据库路径
    NSString *dbPath = [document stringByAppendingPathComponent:@"尸鬼封印.sqlite"];
    NSLog(@"沙盒：%@", dbPath);
    
    // 打开数据库
    int result = sqlite3_open(dbPath.UTF8String, &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }
    
}

- (void)createTable {
    
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists Student(number integer primary key autoincrement, icon_url text, name text, placeholder text, subscribe_count integer, total_updates integer)"];
    // 让数据库执行sql语句(数据库必须是打开的状态)
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
}

- (void)insertModel:(LYFDiscoverModel *)model {
    //sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Student(icon_url, name, placeholder, subscribe_count, total_updates) values('%@', '%@', '%@', '%ld', '%ld')", model.icon_url, model.name, model.placeholder, model.subscribe_count, model.total_updates];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}

- (void)deleteModel:(LYFDiscoverModel *)model {
    // sql 语句
    // where 条件删除
    // delete from Student 删除整个表单数据
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Student where name = '%@'", model.name];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
}

//- (void)updateTable:(LYFDiscoverModel *)model {
//    //sql
////    NSString *sqlStr = [NSString stringWithFormat:@"update Student set sex = '%@', age = %ld where name = 'JAY'", model.sex, model.age];
////    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
////    if (result == SQLITE_OK) {
////        NSLog(@"更新成功");
////    } else {
////        NSLog(@"更新失败");
////    }
//}

- (NSMutableArray *)selectTable {
    // 查询数据逻辑
    // 1.从数据库中读取数据
    // 2.读取出来的数据给model赋值
    // 3.返回装满model的数组
    
    //sql语句
    // * 通配符 把* 改为字段名（name， sex）
    NSString *sqlStre = [NSString stringWithFormat:@"select * from Student"];
    // 创建一个跟随指针，用来遍历数据库中的每一条数据
    sqlite3_stmt *stmt = nil;
    
    // 进入查询
    // 第三个参数：字段的长度没有限制
    int result = sqlite3_prepare_v2(dbPoint, sqlStre.UTF8String, -1, &stmt, NULL);
    
    // 创建一个数组
    NSMutableArray *arrModel = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 遍历
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从数据库中取出数据
            const unsigned char *icon_url = sqlite3_column_text(stmt, 1);
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            const unsigned char *placeholder = sqlite3_column_text(stmt, 3);
            int subscribe_count = sqlite3_column_int(stmt, 4);
            int total_updates = sqlite3_column_int(stmt, 5);
            
            // 创建model 给model赋值
            LYFDiscoverModel *model = [[LYFDiscoverModel alloc] init];
            model.icon_url = [NSString stringWithUTF8String:(const char *)icon_url];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.placeholder = [NSString stringWithUTF8String:(const char *)placeholder];
            model.subscribe_count = subscribe_count;
            model.total_updates = total_updates;
            
            [arrModel addObject:model];
        }
    } else {
        NSLog(@"查询失败");
    }
    return arrModel;
}

@end
